package com.springboot_gradrecruit.mapper;


import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.springboot_gradrecruit.model.entity.Job;
import com.springboot_gradrecruit.model.vo.chart.CityAverageSalary;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

/**
* @author zr
* @description 针对表【job】的数据库操作Mapper
* @createDate 2024-11-04 15:46:30
* @Entity generator.domain.Job
*/
@Mapper
public interface JobMapper extends BaseMapper<Job> {

    @Select("SELECT " +
            "AVG(CASE WHEN job_location like '%北京%' THEN job_salary END) AS beijingAverageSalary, " +
            "AVG(CASE WHEN job_location like '%上海%' THEN job_salary END) AS shanghaiAverageSalary, " +
            "AVG(CASE WHEN job_location like '%广州%' THEN job_salary END) AS guangzhouAverageSalary, " +
            "AVG(CASE WHEN job_location like '%深圳%' THEN job_salary END) AS shenzhenAverageSalary, " +
            "AVG(CASE WHEN job_location like '%杭州%' THEN job_salary END) AS hangzhouAverageSalary, " +
            "AVG(CASE WHEN job_location like '%南京%' THEN job_salary END) AS nanjingAverageSalary, " +
            "AVG(CASE WHEN job_location like '%重庆%' THEN job_salary END) AS chongqingAverageSalary, " +
            "AVG(CASE WHEN job_location like '%武汉%' THEN job_salary END) AS wuhanAverageSalary, " +
            "AVG(CASE WHEN job_location like '%苏州%' THEN job_salary END) AS suzhouAverageSalary, " +
            "AVG(CASE WHEN job_location like '%天津%' THEN job_salary END) AS tianjinAverageSalary, " +
            "AVG(CASE WHEN job_location like '%长沙%' THEN job_salary END) AS changshaAverageSalary, " +
            "AVG(CASE WHEN job_location like '%成都%' THEN job_salary END) AS chengduAverageSalary " +
            "FROM job")
    CityAverageSalary queryAverageSalariesByCities();
}




